﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using MallInteractiveMap.Common;
using MallInteractiveMap.DTO;
using MallInteractiveMap.Language;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace MallInteractiveMap.ServerDataHandler
{
    public class ServerDatabaseHandler : BaseDatabaseHandler
    {
        /// <summary>
        /// returns dataset on the basis of given table name. 
        /// paramlist will be added to the query and the logical operator of the where clause is "AND"
        /// </summary>
        /// <param name="tableName">Name of the table to be fatched</param>
        /// <param name="pParams">parameters for the where clause</param>
        /// <returns></returns>
        public DataSet GetData(string tableName, QueryParamList pParams, ref string pErrString)
        {
            string query = "SELECT * FROM " + tableName;
            AddWhereClause(ref query, pParams);
            return LoadDataSet(query, pParams, tableName, ref pErrString);
        }

        public void ExecuteQuery(string query, QueryParamList pParams, ref string pErrString)
        {
            DBExecCommandEx(query, pParams, ref pErrString);
        }
        private void AddWhereClause(ref string query, QueryParamList pParams)
        {
            query += " WHERE LanguageType=" + (int)LanguageLoader.CurrentLanguageType;
            if (pParams.Count > 0)
            {
                foreach (QueryParamObj item in pParams)
                {
                    query += " AND " + item.ParamName + " = @" + item.ParamName;
                }
            }
        }

        public void SetData(QueryParamList pParam, string spName, ref string pErrString)
        {
            DBExecStoredProc(spName, pParam, ref pErrString);
        }

        #region User
        public List<DTOUser> GetUsers(QueryParamList pParam, ref string pErrString)
        {
            string query = "SELECT Id, Name, LoginId, Password, Email, SecretAnswer, LastUpdated FROM [User]";
            AddWhereClause(ref query, pParam);
            DbDataReader reader = GetDBQueryReader(query, pParam);
            return PopulateUsers(reader);
        }
        public bool IsValidUser(QueryParamList pParam, ref string pErrString)
        {
            string query = "SELECT count(Id) FROM [User]";
            AddWhereClause(ref query, pParam);
            object count = DBExecuteScalar(query, pParam, ref pErrString);
            return count != DBNull.Value ? Convert.ToInt16(count) > 0 : false;
        }
        private List<DTOUser> PopulateUsers(DbDataReader oDbDataReader)
        {
            List<DTOUser> lst = new List<DTOUser>();
            while (oDbDataReader.Read())
            {
                DTOUser oDTOUser = new DTOUser();
                oDTOUser.Id = oDbDataReader["Id"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["Id"]) : oDTOUser.Id;
                oDTOUser.Name = oDbDataReader["Name"] != DBNull.Value ? Convert.ToString(oDbDataReader["Name"]) : oDTOUser.Name;
                oDTOUser.LoginId = oDbDataReader["LoginId"] != DBNull.Value ? Convert.ToString(oDbDataReader["LoginId"]) : oDTOUser.LoginId;
                oDTOUser.Password = oDbDataReader["Password"] != DBNull.Value ? Convert.ToString(oDbDataReader["Password"]) : oDTOUser.Password;
                oDTOUser.Email = oDbDataReader["Email"] != DBNull.Value ? Convert.ToString(oDbDataReader["Email"]) : oDTOUser.Email;
                oDTOUser.SecretAnswer = oDbDataReader["SecretAnswer"] != DBNull.Value ? Convert.ToString(oDbDataReader["SecretAnswer"]) : oDTOUser.SecretAnswer;
                oDTOUser.LastUpdated = oDbDataReader["LastUpdated"] != DBNull.Value ? Convert.ToDateTime(oDbDataReader["LastUpdated"]) : oDTOUser.LastUpdated;
                lst.Add(oDTOUser);
            }
            oDbDataReader.Close();
            return lst;
        }
        #endregion

        #region Mall
        public List<DTOMall> GetMalls(QueryParamList pParam, ref string pErrString)
        {
            string query = "SELECT Id, Name, Logo, Address, Telephone, Email, ContactPerson, Description, LastUpdated, FileName, FileType FROM Mall";
            AddWhereClause(ref query, pParam);
            DbDataReader reader = GetDBQueryReader(query, pParam);
            return PopulateMalls(reader);
        }
        private List<DTOMall> PopulateMalls(DbDataReader oDbDataReader)
        {
            List<DTOMall> lst = new List<DTOMall>();
            while (oDbDataReader.Read())
            {
                DTOMall oDTOMall = new DTOMall();
                oDTOMall.Id = oDbDataReader["Id"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["Id"]) : oDTOMall.Id;
                oDTOMall.Name = oDbDataReader["Name"] != DBNull.Value ? Convert.ToString(oDbDataReader["Name"]) : oDTOMall.Name;
                oDTOMall.Logo = oDbDataReader["Logo"] != DBNull.Value ? (Byte[])(oDbDataReader["Logo"]) : oDTOMall.Logo;
                oDTOMall.Address = oDbDataReader["Address"] != DBNull.Value ? Convert.ToString(oDbDataReader["Address"]) : oDTOMall.Address;
                oDTOMall.Telephone = oDbDataReader["Telephone"] != DBNull.Value ? Convert.ToString(oDbDataReader["Telephone"]) : oDTOMall.Telephone;
                oDTOMall.Email = oDbDataReader["Email"] != DBNull.Value ? Convert.ToString(oDbDataReader["Email"]) : oDTOMall.Email;
                oDTOMall.ContactPerson = oDbDataReader["ContactPerson"] != DBNull.Value ? Convert.ToString(oDbDataReader["ContactPerson"]) : oDTOMall.ContactPerson;
                oDTOMall.Description = oDbDataReader["Description"] != DBNull.Value ? Convert.ToString(oDbDataReader["Description"]) : oDTOMall.Description;
                oDTOMall.LastUpdated = oDbDataReader["LastUpdated"] != DBNull.Value ? Convert.ToDateTime(oDbDataReader["LastUpdated"]) : oDTOMall.LastUpdated;
                oDTOMall.FileName = oDbDataReader["FileName"] != DBNull.Value ? Convert.ToString(oDbDataReader["FileName"]) : oDTOMall.FileName;
                oDTOMall.FileType = oDbDataReader["FileType"] != DBNull.Value ? Convert.ToString(oDbDataReader["FileType"]) : oDTOMall.Description;

                lst.Add(oDTOMall);
            }
            oDbDataReader.Close();
            return lst;
        }
        #endregion

        #region Category
        public List<DTOCategory> GetCategorys(QueryParamList pParam, ref string pErrString)
        {
            string query = "SELECT Id, Name, Description, LastUpdated FROM Category";
            AddWhereClause(ref query, pParam);
            DbDataReader reader = GetDBQueryReader(query, pParam);
            return PopulateCategorys(reader);
        }
        private List<DTOCategory> PopulateCategorys(DbDataReader oDbDataReader)
        {
            List<DTOCategory> lst = new List<DTOCategory>();
            while (oDbDataReader.Read())
            {
                DTOCategory oDTOCategory = new DTOCategory();
                oDTOCategory.Id = oDbDataReader["Id"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["Id"]) : oDTOCategory.Id;
                oDTOCategory.Name = oDbDataReader["Name"] != DBNull.Value ? Convert.ToString(oDbDataReader["Name"]) : oDTOCategory.Name;
                oDTOCategory.Description = oDbDataReader["Description"] != DBNull.Value ? Convert.ToString(oDbDataReader["Description"]) : oDTOCategory.Description;
                oDTOCategory.LastUpdated = oDbDataReader["LastUpdated"] != DBNull.Value ? Convert.ToDateTime(oDbDataReader["LastUpdated"]) : oDTOCategory.LastUpdated;
                lst.Add(oDTOCategory);
            }
            oDbDataReader.Close();
            return lst;
        }
        #endregion

        #region Map
        public List<DTOMap> GetMaps(QueryParamList pParam, ref string pErrString)
        {
            string query = "SELECT Id, Title, ShopCount, Description, LastUpdated FROM Map";
            AddWhereClause(ref query, pParam);
            DbDataReader reader = GetDBQueryReader(query, pParam);
            return PopulateMaps(reader);
        }
        private List<DTOMap> PopulateMaps(DbDataReader oDbDataReader)
        {
            List<DTOMap> lst = new List<DTOMap>();
            while (oDbDataReader.Read())
            {
                DTOMap oDTOMap = new DTOMap();
                oDTOMap.Id = oDbDataReader["Id"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["Id"]) : oDTOMap.Id;
                oDTOMap.Title = oDbDataReader["Title"] != DBNull.Value ? Convert.ToString(oDbDataReader["Title"]) : oDTOMap.Title;
                oDTOMap.ShopCount = oDbDataReader["ShopCount"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["ShopCount"]) : oDTOMap.ShopCount;
                oDTOMap.Description = oDbDataReader["Description"] != DBNull.Value ? Convert.ToString(oDbDataReader["Description"]) : oDTOMap.Description;
                oDTOMap.LastUpdated = oDbDataReader["LastUpdated"] != DBNull.Value ? Convert.ToDateTime(oDbDataReader["LastUpdated"]) : oDTOMap.LastUpdated;
                lst.Add(oDTOMap);
            }
            oDbDataReader.Close();
            return lst;
        }
        #endregion

        #region Floor
        public List<DTOFloor> GetFloors(QueryParamList pParam, ref string pErrString)
        {
            string query = "SELECT Id, MallId, MapId, Name, FloorIndex, Description, LastUpdated FROM Floor";
            AddWhereClause(ref query, pParam);
            List<DTOMap> mapList = GetMaps(new QueryParamList(), ref pErrString);
            DbDataReader reader = GetDBQueryReader(query, pParam);
            return PopulateFloors(reader, mapList);
        }
        private List<DTOFloor> PopulateFloors(DbDataReader oDbDataReader, List<DTOMap> mapList)
        {
            List<DTOFloor> lst = new List<DTOFloor>();
            while (oDbDataReader.Read())
            {
                DTOFloor oDTOFloor = new DTOFloor();
                oDTOFloor.Id = oDbDataReader["Id"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["Id"]) : oDTOFloor.Id;
                oDTOFloor.MallId = oDbDataReader["MallId"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["MallId"]) : oDTOFloor.MallId;
                oDTOFloor.MapId = oDbDataReader["MapId"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["MapId"]) : oDTOFloor.MapId;
                oDTOFloor.Name = oDbDataReader["Name"] != DBNull.Value ? Convert.ToString(oDbDataReader["Name"]) : oDTOFloor.Name;
                oDTOFloor.FloorIndex = oDbDataReader["FloorIndex"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["FloorIndex"]) : oDTOFloor.FloorIndex;
                oDTOFloor.Description = oDbDataReader["Description"] != DBNull.Value ? Convert.ToString(oDbDataReader["Description"]) : oDTOFloor.Description;
                oDTOFloor.LastUpdated = oDbDataReader["LastUpdated"] != DBNull.Value ? Convert.ToDateTime(oDbDataReader["LastUpdated"]) : oDTOFloor.LastUpdated;
               // oDTOFloor.MapTitle = mapList.First(m => m.Id == oDTOFloor.MapId).Title;
                lst.Add(oDTOFloor);
            }
            oDbDataReader.Close();
            return lst;
        }

        public int GetFloorId()
        {
            string error=null;
            object id = DBExecuteScalar("SELECT MAX(Id) FROM Floor", new QueryParamList(),ref error);
            return id == DBNull.Value ? -1: Convert.ToInt32(id);
        }
        #endregion

        #region Shop
        public List<DTOShop> GetShops(QueryParamList pParam, ref string pErrString)
        {
            string query = "SELECT Id, CategoryId, FloorId, Title, ShopNo, Location, Phone, Fax, Email, ContactPerson, Description, LastUpdated FROM Shop";
            AddWhereClause(ref query, pParam);
            List<DTOCategory> categoryList = GetCategorys(new QueryParamList(), ref pErrString);
            List<DTOFloor> floorList = GetFloors(new QueryParamList(), ref pErrString);
            DbDataReader reader = GetDBQueryReader(query, pParam);
            return PopulateShops(reader, categoryList, floorList);
        }
        public List<DTOShop> SearchShops(string searchQuery)
        {
            string query = "SELECT Id, CategoryId, FloorId, Title, ShopNo, Location, Phone, Fax, Email, ContactPerson, Description, LastUpdated FROM Shop";
            query =
                string.Format("{0} WHERE  LanguageType={2} AND ([Description] LIKE '%{1}%' OR [Title] LIKE '%{1}%' OR [ShopNo] LIKE '%{1}%')",
                              query, searchQuery, (int)LanguageLoader.CurrentLanguageType);
            string pErrString = string.Empty; ;
            var queryParamList = new QueryParamList();
            List<DTOCategory> categoryList = GetCategorys(queryParamList, ref pErrString);
            List<DTOFloor> floorList = GetFloors(queryParamList, ref pErrString);
            DbDataReader reader = GetDBQueryReader(query, queryParamList);
            return PopulateShops(reader, categoryList, floorList);
        }
        private List<DTOShop> PopulateShops(DbDataReader oDbDataReader, List<DTOCategory> categoryList, List<DTOFloor> floorList)
        {
            List<DTOShop> lst = new List<DTOShop>();
            while (oDbDataReader.Read())
            {
                DTOShop oDTOShop = new DTOShop();
                oDTOShop.Id = oDbDataReader["Id"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["Id"]) : oDTOShop.Id;
                oDTOShop.CategoryId = oDbDataReader["CategoryId"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["CategoryId"]) : oDTOShop.CategoryId;
                oDTOShop.FloorId = oDbDataReader["FloorId"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["FloorId"]) : oDTOShop.FloorId;
                oDTOShop.Title = oDbDataReader["Title"] != DBNull.Value ? Convert.ToString(oDbDataReader["Title"]) : oDTOShop.Title;
                oDTOShop.ShopNo = oDbDataReader["ShopNo"] != DBNull.Value ? Convert.ToString(oDbDataReader["ShopNo"]) : oDTOShop.ShopNo;
                oDTOShop.Location = oDbDataReader["Location"] != DBNull.Value ? Convert.ToString(oDbDataReader["Location"]) : oDTOShop.Location;
                oDTOShop.Phone = oDbDataReader["Phone"] != DBNull.Value ? Convert.ToString(oDbDataReader["Phone"]) : oDTOShop.Phone;
                oDTOShop.Fax = oDbDataReader["Fax"] != DBNull.Value ? Convert.ToString(oDbDataReader["Fax"]) : oDTOShop.Fax;
                oDTOShop.Email = oDbDataReader["Email"] != DBNull.Value ? Convert.ToString(oDbDataReader["Email"]) : oDTOShop.Email;
                oDTOShop.ContactPerson = oDbDataReader["ContactPerson"] != DBNull.Value ? Convert.ToString(oDbDataReader["ContactPerson"]) : oDTOShop.ContactPerson;
                oDTOShop.Description = oDbDataReader["Description"] != DBNull.Value ? Convert.ToString(oDbDataReader["Description"]) : oDTOShop.Description;
                oDTOShop.LastUpdated = oDbDataReader["LastUpdated"] != DBNull.Value ? Convert.ToDateTime(oDbDataReader["LastUpdated"]) : oDTOShop.LastUpdated;
                oDTOShop.CategoryName = categoryList.First(c => c.Id == oDTOShop.CategoryId).Name;
                oDTOShop.FloorName = floorList.First(f => f.Id == oDTOShop.FloorId).Name;
                lst.Add(oDTOShop);
            }
            oDbDataReader.Close();
            return lst;
        }
        #endregion

        #region Shop Photos
        public List<DTOShopPhotos> GetShopPhotos(QueryParamList pParam, ref string pErrString)
        {
            string query = "SELECT Id, ShopId, ShopImage, FileName, FileType, Description, LastUpdated FROM ShopPhotos";
            AddWhereClause(ref query, pParam);
            DbDataReader reader = GetDBQueryReader(query, pParam);
            return PopulateShopPhotos(reader);
        }
        public List<DTOShopPhotos> GetShopPhotos(string shopNo,int floorId, ref string pErrString)
        {
            string query = "SELECT Id, ShopId, ShopImage, FileName, FileType, Description, LastUpdated FROM ShopPhotos "
                            + " WHERE ShopId = (SELECT Id FROM Shop WHERE ShopNo='" + shopNo + "' AND FloorId=" + floorId  +")";
            
            DbDataReader reader = GetDBQueryReader(query, new QueryParamList());
            return PopulateShopPhotos(reader);
        }
        private List<DTOShopPhotos> PopulateShopPhotos(DbDataReader oDbDataReader)
        {
            List<DTOShopPhotos> lst = new List<DTOShopPhotos>();
            while (oDbDataReader.Read())
            {
                DTOShopPhotos oDTOShopPhotos = new DTOShopPhotos();
                oDTOShopPhotos.Id = oDbDataReader["Id"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["Id"]) : oDTOShopPhotos.Id;
                oDTOShopPhotos.ShopId = oDbDataReader["ShopId"] != DBNull.Value ? Convert.ToInt32(oDbDataReader["ShopId"]) : oDTOShopPhotos.ShopId;
                oDTOShopPhotos.ShopImage = oDbDataReader["ShopImage"] != DBNull.Value ? (Byte[])(oDbDataReader["ShopImage"]) : oDTOShopPhotos.ShopImage;
                oDTOShopPhotos.FileName = oDbDataReader["FileName"] != DBNull.Value ? Convert.ToString(oDbDataReader["FileName"]) : oDTOShopPhotos.FileName;
                oDTOShopPhotos.FileType = oDbDataReader["FileType"] != DBNull.Value ? Convert.ToString(oDbDataReader["FileType"]) : oDTOShopPhotos.FileType;
                oDTOShopPhotos.Description = oDbDataReader["Description"] != DBNull.Value ? Convert.ToString(oDbDataReader["Description"]) : oDTOShopPhotos.Description;
                oDTOShopPhotos.LastUpdated = oDbDataReader["LastUpdated"] != DBNull.Value ? Convert.ToDateTime(oDbDataReader["LastUpdated"]) : oDTOShopPhotos.LastUpdated;
                lst.Add(oDTOShopPhotos);
            }
            oDbDataReader.Close();
            return lst;
        }

        public bool SaveShop(DTOShop oShop, ref string error)
        {
            QueryParamList param = new QueryParamList();
            if (!oShop.IsNew)
            {
                param.Add(new QueryParamObj() { ParamName = "Id", DBType = DbType.Int32, ParamValue = oShop.Id });
            }
            else
            {
                param.Add(new QueryParamObj() { ParamName = "LanguageType", DBType = DbType.Int32, ParamValue = LanguageLoader.CurrentLanguageType });
            }
            param.Add(new QueryParamObj() { ParamName = "CategoryId", DBType = DbType.Int32, ParamValue = oShop.CategoryId });
            param.Add(new QueryParamObj() { ParamName = "FloorId", DBType = DbType.Int32, ParamValue = oShop.FloorId });
            param.Add(new QueryParamObj() { ParamName = "Title", DBType = DbType.String, ParamValue = oShop.Title });
            param.Add(new QueryParamObj() { ParamName = "ShopNo", DBType = DbType.String, ParamValue = oShop.ShopNo });
            param.Add(new QueryParamObj() { ParamName = "Location", DBType = DbType.String, ParamValue = oShop.Location });
            param.Add(new QueryParamObj() { ParamName = "Phone", DBType = DbType.String, ParamValue = oShop.Phone });
            param.Add(new QueryParamObj() { ParamName = "Fax", DBType = DbType.String, ParamValue = oShop.Fax });
            param.Add(new QueryParamObj() { ParamName = "Email", DBType = DbType.String, ParamValue = oShop.Email });
            param.Add(new QueryParamObj() { ParamName = "ContactPerson", DBType = DbType.String, ParamValue = oShop.ContactPerson });
            param.Add(new QueryParamObj() { ParamName = "Description", DBType = DbType.String, ParamValue = oShop.Description });
            param.Add(new QueryParamObj() { ParamName = "LastUpdated", DBType = DbType.DateTime, ParamValue = oShop.LastUpdated });

            string spName = oShop.IsNew ? "INSERTShop " : "UPDATEShop";

            Database db = GetSQLDatabase();
            DbConnection connection = db.CreateConnection();
            connection.Open();
            DbTransaction transaction = connection.BeginTransaction();
            try
            {
                int shopId = -1;
                DbCommand cmd = db.GetStoredProcCommand(spName);
                DBExecStoredProcInTran(db, cmd, param, transaction);

                if (oShop.IsNew)
                {
                    object id = DBExecuteScalar("SELECT MAX(Id) FROM Shop", new QueryParamList(), transaction);
                    shopId = id != DBNull.Value ? Convert.ToInt32(id) : 1;
                }
                else
                {
                    shopId = oShop.Id;
                }
                if (oShop.Photos != null && oShop.Photos.Count > 0)
                {
                    foreach (var photos in oShop.Photos)
                    {
                        param = new QueryParamList();
                        param.Add(new QueryParamObj() { ParamName = "ShopId", DBType = DbType.Int32, ParamValue = shopId });
                        param.Add(new QueryParamObj() { ParamName = "ShopImage", DBType = DbType.Binary, ParamValue = photos.ShopImage });
                        param.Add(new QueryParamObj() { ParamName = "FileName", DBType = DbType.String, ParamValue = photos.FileName });
                        param.Add(new QueryParamObj() { ParamName = "FileType", DBType = DbType.String, ParamValue = photos.FileType });
                        param.Add(new QueryParamObj() { ParamName = "Description", DBType = DbType.String, ParamValue = photos.Description });
                        param.Add(new QueryParamObj() { ParamName = "LastUpdated", DBType = DbType.DateTime, ParamValue = photos.LastUpdated });
                        param.Add(new QueryParamObj() { ParamName = "LanguageType", DBType = DbType.Int32, ParamValue = LanguageLoader.CurrentLanguageType });


                        spName = "INSERTShopPhotos";
                        cmd = db.GetStoredProcCommand(spName);
                        DBExecStoredProcInTran(db, cmd, param, transaction);
                    }
                }
                transaction.Commit();
            }
            catch (Exception exp)
            {
                transaction.Rollback();
                error = exp.Message;
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }
        #endregion

        public bool IsShopAvailable(int categoryId)
        {
            string error = null;
            object id = DBExecuteScalar("SELECT COUNT(Id) FROM Shop WHERE CategoryId =" + categoryId, new QueryParamList(), ref error);
            return id == DBNull.Value ? false : (Convert.ToInt32(id) > 0);
        }
    }
}
